//用来操作数据库的
//先安装mysql
//npm install mysql --save

var mysql = require('mysql');
const Database = class Database {
    constructor() {
        //初始化
        this.connection = mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'biaobiao',
            database: 'db_note',
            port: 3306,
            charset: 'UTF8_GENERAL_CI'
        })
    }

    //向note表中插入云笔记数据
    addNote(req, res) {
        this.connection.query('insert into note set ?', { user_id: req.query.id, name: req.query.name, code: req.query.code }, function (err, result) {
            if (err) {
                console.log(err);
            } else {
                console.log('插入数据：', result);
                //把结果发送给客户端
                res.json(result);
            }
        })
    }

    //更新云笔记数据
    updateNote(req, res) {
        this.connection.query('update note set ? where id=' + req.query.sqlid, { name: req.query.name, code: req.query.code }, function (err, result) {
            if (err) {
                console.log(err);
            } else {
                console.log(result);
                //把结果发送给客户端
                res.json(result);
            }
        })
    }

    //根据登录的用户的id获取云笔记的name列表
    getNoteListById(req, res) {
        this.connection.query('select * from note where ? ', { user_id: req.query.id }, function (err, result) {
            if (err) {
                console.log(err);
            } else {
                console.log('12345：', result);
                //把结果发送给客户端
                res.json(result);
            }
        })
    }

    //根据登录的用户的id和name获取特定的笔记
    getNoteListByIdAndName(req, res) {
        // var sql = 'select * from note where user_id= ' + req.query.id+ ' and name like \"%'+ req.query.name+'%\";';
        var sql_safe = 'select * from note where user_id=  ? and name like  ? ;';
        
        this.connection.query(sql_safe,[req.query.id,"%"+req.query.name+"%"] ,function (err, result) {
            if (err) {
                console.log(err);
            } else {
                console.log('12345：', result, sql_safe);
                //把结果发送给客户端
                res.json(result);
            }
        })
    }

    deleteNote(req,res){
        var sql_safe = 'delete from note where user_id=  ? and name =  ? ;';
        this.connection.query(sql_safe,[req.query.id,req.query.name] ,function (err, result) {
            if (err) {
                console.log(err);
            } else {
                console.log('12345：', result, sql_safe);
                //把结果发送给客户端
                res.json(result);
            }
        })
    }
}

module.exports = Database;